﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using GenericTools;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.ComponentModel;
using System.IO;
using ReportingManagement.Components;
using ReportingManagement.Models;
using System.Drawing;
using GenericTools.Models;
using DataModels.Common;

namespace ReportingManagement
{
    public partial class MainWindow
    {
        #region Constants
        const string BOOLEAN_TRUE = "TRUE";
        const string BOOLEAN_FALSE = "FALSE";
        const string DIGIT_TRUE = "1";
        const string DEBUGGING_OUTPUT_FOLDER = @"C:\IQSMS\Testing";

        const int SAPR_START_MONTH = 10;
        const int SAPR_END_MONTH = 3;

        const int APR_START_MONTH = 10;
        const int APR_END_MONTH = 9;

        const int SECOND_SAPR_START_MONTH = 4;
        const int SECOND_SAPR_END_MONTH = 9;
        #endregion

        #region Variables
        string MAX_ANC = String.Format("MAX_{0}", ExcelTemplateReport.ANC_TABLE);
        string MAX_PCR = String.Format("MAX_{0}", ExcelTemplateReport.PCR_TABLE);
        string MAX_MAT = String.Format("MAX_{0}", ExcelTemplateReport.MAT_TABLE);
        string MAX_MC = String.Format("MAX_{0}", ExcelTemplateReport.MC_TABLE);
        string MAX_TC = String.Format("MAX_{0}", ExcelTemplateReport.TC_TABLE);

        bool generateAPR = false;
        bool generateSAPR = false; 
        bool useSAPRAsBaseline = false;
        bool fineTuneAPR = true;
        List<ExcelTemplateReport> excelTemplateReports = new List<ExcelTemplateReport>(); 
        private bool tabPagePromisReportingLoaded = false;
        bool useDigitForBoolean = false;

        int calculatingMethod = DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_DEFAULT;
        string[] quarters = null;
        string[] max_quarters = null;
        #endregion

        #region Private Helper Methods
        private void GenerateSAPRComboBox()
        {
            cboSAPRDuration.Items.Clear();
            DataTable dataTable = Utilities.GetAllReportYears();
            foreach (DataRow r in dataTable.Rows)
            {
                int year = Int32.Parse(r["Year"].ToString());
                ListBoxItem item = new ListBoxItem();
                item.Value = year;
                item.Text = String.Format("October {0} - March {1}", year, ++year);
                
                DateTime t = DateTime.Parse(String.Format("{0}-03-31", year));
                if (t < DateTime.Now) cboSAPRDuration.Items.Add(item);
            }

            cboSAPRDuration.SelectedIndex = cboSAPRDuration.Items.Count > 0 ? cboSAPRDuration.Items.Count - 1 : -1;
        }

        private void GenerateAPRComboBox()
        {
            cboAPRDuration.Items.Clear();
            DataTable dataTable = Utilities.GetAllReportYears();
            foreach (DataRow r in dataTable.Rows)
            {
                int year = Int32.Parse(r["Year"].ToString());
                ListBoxItem item = new ListBoxItem();
                item.Value = year;
                item.Text = String.Format("October {0} - September {1}", year, ++year);
                
                DateTime t = DateTime.Parse(String.Format("{0}-09-30", year));
                if (t < DateTime.Now) cboAPRDuration.Items.Add(item);
            }

            cboAPRDuration.SelectedIndex = cboAPRDuration.Items.Count > 0 ? cboAPRDuration.Items.Count - 1 : -1;
        }

        private static int GetOptimalDropDownWidth(ComboBox myCombo)
        {
            int maxWidth = myCombo.Width;
            int temp = 0;
            foreach (var obj in myCombo.Items)
            {
                var item = (ExcelTemplateReport)obj;
                string text = item.ToString();
                temp = TextRenderer.MeasureText(text, myCombo.Font).Width;
                if (temp > maxWidth) maxWidth = temp;
            }
            return maxWidth;
        }

        /// <summary>
        /// The returned list is sorted such that main templates are the top.
        /// </summary>
        /// <returns></returns>
        private List<ExcelTemplateReport> GetSelectedTemplateReports()
        {
            var templates = new List<ExcelTemplateReport>();
            #region If generating SAPR
            if (rdoGenerateSAPR.Checked)
            {
                Dictionary<int, int> selectedTemplates = new Dictionary<int, int>();
                //selectedTemplates[ExcelTemplateReport.INVALID_TEMPLATE_REPORT_ID] = ExcelTemplateReport.INVALID_TEMPLATE_REPORT_ID;

                if (btnSelectSAPRTemplateFiles.Tag != null)
                {
                    List<TemplateSelectorViewModel> chosenTemplates = (List<TemplateSelectorViewModel>) btnSelectSAPRTemplateFiles.Tag;
                    foreach (var t in chosenTemplates)
                    {
                        //var template = new ExcelTemplateReport(t.ExcelTemplateId);
                        var template = t.Data as ExcelTemplateReport;
                        templates.Add(template);
                        selectedTemplates[t.ExcelTemplateId] = t.ExcelTemplateId;
                    }
                }

                //if (cboSAPRTemplate1.SelectedItem != null)
                //{
                //    int id = Int32.Parse(cboSAPRTemplate1.SelectedValue.ToString());
                //    if (!selectedTemplates.ContainsKey(id))
                //    {
                //        var template = new ExcelTemplateReport(id);
                //        templates.Add(template);
                //        selectedTemplates[id] = id;
                //    }
                //}

                //if (cboSAPRTemplate2.SelectedItem != null)
                //{
                //    int id = Int32.Parse(cboSAPRTemplate2.SelectedValue.ToString());
                //    if (!selectedTemplates.ContainsKey(id))
                //    {
                //        var template = new ExcelTemplateReport(id);
                //        templates.Add(template);
                //        selectedTemplates[id] = id;
                //    }
                //}
            }
            #endregion

            #region If generating APR
            if (rdoGenerateAPR.Checked)
            {
                Dictionary<int, int> selectedTemplates = new Dictionary<int, int>();
                selectedTemplates[ExcelTemplateReport.INVALID_TEMPLATE_REPORT_ID] = ExcelTemplateReport.INVALID_TEMPLATE_REPORT_ID;

                if (btnSelectAPRTemplateFiles.Tag != null)
                {
                    List<TemplateSelectorViewModel> chosedTemplates = (List<TemplateSelectorViewModel>)btnSelectAPRTemplateFiles.Tag;
                    foreach (var t in chosedTemplates)
                    {
                        //var template = new ExcelTemplateReport(t.ExcelTemplateId);
                        var template = t.Data as ExcelTemplateReport;
                        templates.Add(template);
                        selectedTemplates[t.ExcelTemplateId] = t.ExcelTemplateId;
                    }
                }

                //if (cboAPRTemplate1.SelectedItem != null)
                //{
                //    int id = Int32.Parse(cboAPRTemplate1.SelectedValue.ToString());
                //    if (!selectedTemplates.ContainsKey(id))
                //    {
                //        var template = new ExcelTemplateReport(id);
                //        templates.Add(template);
                //        selectedTemplates[id] = id;
                //    }
                //}

                //if (cboAPRTemplate2.SelectedItem != null)
                //{
                //    int id = Int32.Parse(cboAPRTemplate2.SelectedValue.ToString());
                //    if (!selectedTemplates.ContainsKey(id))
                //    {
                //        var template = new ExcelTemplateReport(id);
                //        templates.Add(template);
                //        selectedTemplates[id] = id;
                //    }
                //}
            }
            #endregion

            // Re-organize the list to make sure main template are at the top
            //if (templates.Count > 1)
            //{
            //    for (int i = 0; i < templates.Count - 1; i++)
            //    {
            //        int currentItem = i;
            //        int nextItem = currentItem + 1;
            //        if (!templates[currentItem].MainTemplate)
            //        {
            //            for (int j = nextItem; j < templates.Count; j++)    // finding the next main template
            //            {
            //                if (templates[j].MainTemplate)
            //                {
            //                    var temp = templates[currentItem];
            //                    templates[currentItem] = templates[j];
            //                    templates[j] = temp;
            //                    break;
            //                }
            //            }
            //        }
            //    }
            //}
            return templates;
        }

        private bool GeneratePromiseSAPR_APR()
        {
            #region Function Constants and Variables
            const int ANC_REPORT_ID = 6;
            const int PCR_REPORT_ID = 8;
            const int MAT_REPORT_ID = 7;
            const int MC_REPORT_ID = 9;
            const int REGION_DISTRICT_FACILITY_COLUMN_COUNT = 3;

            OtherSystem targetSystem = OtherSystem.GetOtherSystem(OtherKnownSystem.PROMIS); 

            int progress = -1;
            //string log = String.Empty;
            object misValue = System.Reflection.Missing.Value;
            completed = false;

            Excel.Application excelApplication = null;
            Excel.Workbooks excelWorkbooks = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Sheets excelSheets = null;
            Excel.Worksheet excelWorksheet = null;

            #endregion       

            try
            {
                excelApplication = new Excel.Application();
                excelWorkbooks = excelApplication.Workbooks; 
                SAPRProgress progressInfo = new SAPRProgress();               
                DataSet allTargetFacilities = new DataSet();

                #region Getting list of target facilities from Excel file
                foreach (var excelTemplate in excelTemplateReports)
                {
                    string templatePath = String.Format(@"{0}\Resources\{1}", Application.StartupPath, excelTemplate.Filename);
                    string destinationFilename = String.Format(@"{0}\{1}", m_SelectedFilePath, excelTemplate.Filename);
                    if (!File.Exists(templatePath))
                    {
                        string message = "Template file for " + excelTemplate.Description + " is missing."
                            + "To continue please put the file in this location:\n"
                            + Application.StartupPath + @"\Resources\" + excelTemplate.Filename;
                        MessageBox.Show(message, AppConfiguration.ApplicationName,
                            MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return false;
                        
                        //if (response == System.Windows.Forms.DialogResult.OK)
                        //{
                        //    var openFileDialog = new OpenFileDialog();
                        //    openFileDialog.Title = "Locate Missing Template";
                        //    openFileDialog.Multiselect = false;
                        //    response = openFileDialog.ShowDialog();
                        //    if (response == System.Windows.Forms.DialogResult.OK) templatePath = openFileDialog.FileName;
                        //    else return false;
                        //}
                        //else return false;
                    }


                    System.IO.File.Copy(templatePath, destinationFilename, true);
                    excelTemplate.OutputFile = destinationFilename;

                    if (excelTemplate.MainTemplate)
                    {                                                
                        excelWorkbook = excelWorkbooks.Open(destinationFilename, 0, false, 5,
                            "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                        if (excelTemplate.Sheets.Count == 1)
                        {
                            //int currentSheetIndex = 5;
                            excelSheets = excelWorkbook.Worksheets;
                            //excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheetIndex);     // 1-based index
                            excelWorksheet = (Excel.Worksheet)excelSheets[excelTemplate.Sheets[0].TemplateSheetName]; 

                            int startingRow = excelTemplate.Sheets[0].FirstRepetitiveRowCellIndex;
                            int startingColumn = excelTemplate.Sheets[0].FirstRepetitiveColumnCellIndex;
                            int rows = -1;
                            //int columns = startingColumn;
                            int columns = REGION_DISTRICT_FACILITY_COLUMN_COUNT;
                            
                            progressInfo.log = "Retrieving list of target facilities from Excel file. Please wait...";
                            progressInfo.value = -1;
                            backgroundWorkerPromis.ReportProgress(progress, progressInfo);

                            DataTable targetFacilities = ReadExcelSheet(excelWorksheet, startingRow, startingColumn, rows, columns);
                            targetFacilities.TableName = String.Format("{0} - {1}", excelTemplate.TemplateID, excelTemplate.Name);
                            allTargetFacilities.Tables.Add(targetFacilities);

                            excelWorkbook.Close(false, misValue, misValue);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                            excelWorkbook = null;
                        }
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                }
                #endregion

                #region Indexing Excel sheet Values
                DataSet indices = new DataSet();
                if (generateAPR && useSAPRAsBaseline)
                {
                    if (rtxtSelectedSAPRBaselineTemplates.Tag != null)
                    {
                        var selectedTemplates = (List<TemplateSelectorViewModel>)rtxtSelectedSAPRBaselineTemplates.Tag;
                        foreach (var t in selectedTemplates)
                        {
                            //ExcelTemplateReport excelTemplate = new ExcelTemplateReport(t.ExcelTemplateId);
                            ExcelTemplateReport excelTemplate = (from et in PromisTemplateSelector.ExcelTemplateReports
                                                                 where et.TemplateID == t.ExcelTemplateId
                                                                 select et).FirstOrDefault();
                            if (excelTemplate.MainTemplate)
                            {
                                if (File.Exists(t.TemplateLocation))
                                {
                                    excelWorkbook = excelWorkbooks.Open(t.TemplateLocation, 0, false, 5,
                                    "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                                    excelSheets = excelWorkbook.Worksheets;
                                    excelWorksheet = (Excel.Worksheet)excelSheets[excelTemplate.Sheets[0].TemplateSheetName];

                                    int startingRow = excelTemplate.Sheets[0].FirstRepetitiveRowCellIndex;
                                    int startingColumn = excelTemplate.Sheets[0].FirstRepetitiveColumnCellIndex;

                                    DataTable indexing = ReadExcelSheet(excelWorksheet, startingRow, startingColumn, String.Empty);
                                    excelWorkbook.Close(false, misValue, misValue);
                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                                    excelWorkbook = null;

                                    indexing.TableName = String.Format("{0} - Baseline: {1}", excelTemplate.TemplateID, excelTemplate.Name);
                                    indices.Tables.Add(indexing);
                                }
                            }

                            if (backgroundWorkerPromis.CancellationPending)
                            {
                                excelWorkbook.Close(true, misValue, misValue);
                                excelApplication.Quit();

                                ReleaseObject(excelWorksheet);
                                ReleaseObject(excelSheets);
                                ReleaseObject(excelWorkbook);
                                ReleaseObject(excelWorkbooks);
                                ReleaseObject(excelApplication);
                                operationCanceled = true;
                                return false;
                            }
                        }
                    }
                }
                #endregion

                #region Get all facilities in particular district, district or nation
                DataTable facilitiesDetails = null;
                DataSet summaryDataSet = new DataSet();

                progressInfo.log = "Retrieving records from database. Please wait...";
                progressInfo.value = -1;
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);

                progressInfo.log = "Retrieving list of facilities. Please wait...";
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                //facilitiesDetails = Utilities.GetAllFacilitiesDetails(true, true);
                //facilitiesDetails = Utilities.GetAllFacilitiesDetails_Alpha();
                //facilitiesDetails = Utilities.GetAllFacilitiesDetails();
                facilitiesDetails = Utilities.GetAllFacilitiesDetailsByKnownSystem(targetSystem.Id);
                if (backgroundWorkerPromis.CancellationPending)
                {
                    excelWorkbook.Close(true, misValue, misValue);
                    excelApplication.Quit();

                    ReleaseObject(excelWorksheet);
                    ReleaseObject(excelSheets);
                    ReleaseObject(excelWorkbook);
                    ReleaseObject(excelWorkbooks);
                    ReleaseObject(excelApplication);
                    operationCanceled = true;
                    return false;
                }
                #endregion

                #region Retrieving Report data
                #region Bulk Retrieval of data
                DateTime startDate = DateTime.Parse(String.Format("{0:0000}-{1:00}-01", sapr_startYear, sapr_startPeriod));
                DateTime endDate = DateTime.Parse(String.Format("{0:0000}-{1:00}-28", sapr_endYear, sapr_endPeriod));

                progress = 0;
                progressInfo.minValue = 0;
                if (sapr_useMaximumValues) progressInfo.maxValue = 8;
                else progressInfo.maxValue = 4;

                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading ANC Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable ANC_Reports = Utilities.GetBulkEachFacilityReport(ANC_REPORT_ID, startDate, endDate);

                progress++;
                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading PCR Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable PCR_Reports = Utilities.GetBulkEachFacilityReport(PCR_REPORT_ID, startDate, endDate);

                progress++;
                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading MAT Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable MAT_Reports = Utilities.GetBulkEachFacilityReport(MAT_REPORT_ID, startDate, endDate);

                progress++;
                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading MC Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable MC_Reports = Utilities.GetBulkEachFacilityReport(MC_REPORT_ID, startDate, endDate);

                DataTable Max_ANC_Reports = null;
                DataTable Max_PCR_Reports = null;
                DataTable Max_MAT_Reports = null;
                DataTable Max_MC_Reports = null;
                if (generateAPR && sapr_useMaximumValues)
                {
                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading ANC Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_ANC_Reports = Utilities.GetBulkEachFacilityReport(ANC_REPORT_ID, sapr_useMax_startDate, sapr_useMax_endDate);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading PCR Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_PCR_Reports = Utilities.GetBulkEachFacilityReport(PCR_REPORT_ID, sapr_useMax_startDate, sapr_useMax_endDate);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading MAT Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_MAT_Reports = Utilities.GetBulkEachFacilityReport(MAT_REPORT_ID, sapr_useMax_startDate, sapr_useMax_endDate);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading MC Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_MC_Reports = Utilities.GetBulkEachFacilityReport(MC_REPORT_ID, sapr_useMax_startDate, sapr_useMax_endDate);
                }
                #endregion

                int facilityCount = facilitiesDetails.Rows.Count;
                int facilityIndex = 0;
                int totalWork = facilityCount;
                progress = 0;
                progressInfo.minValue = 0;
                progressInfo.maxValue = totalWork;
                string template = "{0} in {1} | {2} ({3} of {4}): Loading Report data, please wait...";
                List<DataSet> facilitiesDataSet = new List<DataSet>();
                foreach (DataRow row in facilitiesDetails.Rows)
                {
                    facilityIndex++;
                    string internalFacilityCode = row["InternalFacilityCode"].ToString();
                    string internalFacilityName = row["InternalFacilityName"].ToString();
                    string internalDistrictName = row["InternalDistrictName"].ToString();
                    string internalRegionName = row["InternalRegionName"].ToString();

                    string facilityCode = row["FacilityCode"].ToString();
                    string facilityName = row["FacilityName"].ToString();
                    string facilityType = row["FacilityType"].ToString();
                    string provideDBS = row["ProvideDBS"].ToString();
                    string districtName = row["DistrictName"].ToString();
                    string regionName = row["RegionName"].ToString();

                    DataTable max_DataTemplate = new DataTable();
                    DataColumn c = new DataColumn("Data Description", Type.GetType("System.String"));
                    max_DataTemplate.Columns.Add(c);
                    c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                    max_DataTemplate.Columns.Add(c);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format(template, internalDistrictName, internalRegionName, 
                        internalFacilityName, facilityIndex, facilityCount);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);

                    #region ANC
                    DataSet facilityDataset = new DataSet();
                    DataTable data = null; // this table consists of two column Data Description and ReportData
                    DataTable max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < ANC_Reports.Rows.Count; k++)
                        {
                            DataRow r = ANC_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < ANC_Reports.Columns.Count; i++)
                                {
                                    string columnName = ANC_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                ANC_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_ANC_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_ANC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_ANC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_ANC_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_ANC_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_ANC_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_ANC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_ANC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_ANC_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch //This is just the old fall back, I don't expect it to be used
                    {
                        do
                        {
                            try
                            {
                                data = Utilities.GetFacilityReport(internalFacilityCode,
                                    ANC_REPORT_ID, sapr_startYear, sapr_startPeriod, sapr_endYear, 
                                    sapr_endPeriod, true);
                                break;
                            }
                            catch (Exception ex)
                            {
                                DialogResult response = MessageBox.Show(this, ex.Message,
                                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                            }
                        } while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.ANC_TABLE;
                    if (sapr_useMaximumValues) max_Data.TableName = MAX_ANC;

                    #region This is the Clement special request
                    DataTable anc = data;
                    DataRow anc04Row = anc.Rows[3];
                    DataRow anc08Row = anc.Rows[7];
                    int anc04 = 0;
                    int anc08 = 0;
                    Int32.TryParse(anc04Row[0].ToString(), out anc04);
                    Int32.TryParse(anc08Row[0].ToString(), out anc08);
                    if (anc08 < anc04)
                    {
                        DataRow anc06Row = anc.Rows[5];
                        int anc06 = 0;
                        Int32.TryParse(anc06Row[0].ToString(), out anc06);
                        if ((anc04 == anc06) && (anc08 == 0))
                        {
                            DataRow anc07Row = anc.Rows[6];
                            int anc07 = 0;
                            Int32.TryParse(anc07Row[0].ToString(), out anc07);

                            anc06Row[0] = anc07;
                            anc07Row[0] = anc08;
                            anc08Row[0] = anc06;
                        }
                    }

                    if (sapr_useMaximumValues)
                    {
                        anc = max_Data;
                        anc04Row = anc.Rows[3];
                        anc08Row = anc.Rows[7];
                        anc04 = 0;
                        anc08 = 0;
                        Int32.TryParse(anc04Row[0].ToString(), out anc04);
                        Int32.TryParse(anc08Row[0].ToString(), out anc08);
                        if (anc08 < anc04)
                        {
                            DataRow anc06Row = anc.Rows[5];
                            int anc06 = 0;
                            Int32.TryParse(anc06Row[0].ToString(), out anc06);
                            if ((anc04 == anc06) && (anc08 == 0))
                            {
                                DataRow anc07Row = anc.Rows[6];
                                int anc07 = 0;
                                Int32.TryParse(anc07Row[0].ToString(), out anc07);

                                anc06Row[0] = anc07;
                                anc07Row[0] = anc08;
                                anc08Row[0] = anc06;
                            }
                        }
                    }
                    #endregion

                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues) facilityDataset.Tables.Add(max_Data);

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region PCR
                    data = null;
                    max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < PCR_Reports.Rows.Count; k++)
                        {
                            DataRow r = PCR_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < PCR_Reports.Columns.Count; i++)
                                {
                                    string columnName = PCR_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                PCR_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_PCR_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_PCR_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_PCR_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_PCR_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_PCR_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_PCR_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_PCR_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_PCR_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_PCR_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch
                    {
                        do
                        {
                            try
                            {
                                data = Utilities.GetFacilityReport(internalFacilityCode,
                                    PCR_REPORT_ID, sapr_startYear, sapr_startPeriod, sapr_endYear, 
                                    sapr_endPeriod, true);
                                break;
                            }
                            catch (Exception ex)
                            {
                                DialogResult response = MessageBox.Show(this, ex.Message,
                                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                            }
                        } while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.PCR_TABLE;
                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues)
                    {
                        max_Data.TableName = MAX_PCR;
                        facilityDataset.Tables.Add(max_Data);
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region MAT
                    data = null;
                    max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < MAT_Reports.Rows.Count; k++)
                        {
                            DataRow r = MAT_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < MAT_Reports.Columns.Count; i++)
                                {
                                    string columnName = MAT_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                MAT_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_MAT_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_MAT_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_MAT_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_MAT_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_MAT_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_MAT_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_MAT_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_MAT_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_MAT_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch
                    {
                        do
                        {
                            try
                            {
                                data = Utilities.GetFacilityReport(internalFacilityCode, MAT_REPORT_ID,
                                    sapr_startYear, sapr_startPeriod, sapr_endYear, sapr_endPeriod, true);
                                break;
                            }
                            catch (Exception ex)
                            {
                                DialogResult response = MessageBox.Show(this, ex.Message,
                                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                            }
                        } while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.MAT_TABLE;
                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues)
                    {
                        max_Data.TableName = MAX_MAT;
                        facilityDataset.Tables.Add(max_Data);
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region MC
                    data = null;
                    max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < MC_Reports.Rows.Count; k++)
                        {
                            DataRow r = MC_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < MC_Reports.Columns.Count; i++)
                                {
                                    string columnName = MC_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                MC_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_MC_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_MC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_MC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_MC_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_MC_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_MC_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_MC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_MC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_MC_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch
                    {
                        do
                        {
                            try
                            {
                                data = Utilities.GetFacilityReport(internalFacilityCode, MC_REPORT_ID,
                                    sapr_startYear, sapr_startPeriod, sapr_endYear, sapr_endPeriod, true);
                                break;
                            }
                            catch (Exception ex)
                            {
                                DialogResult response = MessageBox.Show(this, ex.Message,
                                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                            }
                        } while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.MC_TABLE;
                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues)
                    {
                        max_Data.TableName = MAX_MC;
                        facilityDataset.Tables.Add(max_Data);
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region Generating Facility info table
                    DataTable facilityInfo = new DataTable();
                    DataColumn column = new DataColumn(FACCODE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(FACILITY_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DISTRICT_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(REGION_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(FACILITY_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(PROVIDEDBS, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(HOSPITAL, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DISPENSARY, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(HEALTH_CENTRE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(PMTCTSITE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(ANCSERVICES, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);

                    column = new DataColumn(INTERNAL_FACILITY_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(INTERNAL_DISTRICT_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(INTERNAL_REGION_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);

                    string[] values = { 
										  facilityCode, facilityName, districtName, regionName, 
										  facilityType, provideDBS, String.Empty, String.Empty, 
										  String.Empty, useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE, "TRUE",
                                          internalFacilityName, internalDistrictName, internalRegionName
									  };
                    facilityInfo.Rows.Add(values);
                    facilityInfo.TableName = ExcelTemplateReport.FACILITY_INFO_TABLE;
                    #endregion
                    facilityDataset.Tables.Add(facilityInfo);

                    facilitiesDataSet.Add(facilityDataset);
                }
                #endregion

                DataTable index = null;
                if (generateAPR && useSAPRAsBaseline)
                {
                    index = indices.Tables[0].Clone();
                    foreach (DataTable table in indices.Tables) index.Merge(table);
                }

                foreach (var excelTemplate in excelTemplateReports)
                {
                    if (excelTemplate.MainTemplate)
                    {
                        #region Retrieving TargetFacilities DataTable
                        string targetTableName = String.Format("{0} - {1}", excelTemplate.TemplateID, excelTemplate.Name);
                        DataTable targetFacilities = null;
                        foreach (DataTable table in allTargetFacilities.Tables)
                        {
                            if (table.TableName == targetTableName)
                            {
                                targetFacilities = table;
                                break;
                            }
                        }
                        #endregion

                        #region Retrieving Index DataTable
                        //string targetIndexTableName = String.Format("{0} - Baseline: {1}", excelTemplate.TemplateID, excelTemplate.Name);
                        //DataTable index = null;
                        //foreach (DataTable table in indices.Tables)
                        //{
                        //    if (table.TableName == targetIndexTableName)
                        //    {
                        //        index = table;
                        //        break;
                        //    }
                        //}
                        #endregion

                        int columnOffset = 0;
                        int rowOffset = 0;
                        template = @"Writing to Facility data to Excel File: {2} [{0} of {1}]";
                        facilityIndex = 0;
                        facilityCount = targetFacilities.Rows.Count;

                        progress = 0;
                        progressInfo.minValue = 0;
                        progressInfo.maxValue = facilityCount;

                        #region Processing Every Facility in the Template
                        foreach (DataRow targetFacility in targetFacilities.Rows)
                        {
                            facilityIndex++;
                            progressInfo.log = String.Format(template, facilityIndex, facilityCount, excelTemplate.Filename);
                            progressInfo.value = facilityIndex;
                            backgroundWorkerPromis.ReportProgress(facilityIndex, progressInfo);

                            string targetRegion = targetFacility[0].ToString();
                            string targetDistrict = targetFacility[1].ToString();
                            string targetFacilityName = targetFacility[2].ToString();

                            FindAndWriteFacilityData(facilitiesDataSet, targetRegion, targetDistrict, targetFacilityName, 
                                excelTemplate, index, rowOffset, columnOffset);
                            rowOffset++;

                            //excelWorkbook.Save();
                            if (backgroundWorkerPromis.CancellationPending)
                            {
                                excelWorkbook.Close(true, misValue, misValue);
                                excelApplication.Quit();

                                ReleaseObject(excelWorksheet);
                                ReleaseObject(excelSheets);
                                ReleaseObject(excelWorkbook);
                                ReleaseObject(excelWorkbooks);
                                ReleaseObject(excelApplication);
                                operationCanceled = true;
                                return false;
                            }
                        }
                        #endregion
                    }
                    else
                    {
                        WriteDataForMissingFacilities(facilitiesDataSet, excelTemplate);
                    }

                    // Release some excel resources related to ExcelTemplateReport object.
                    // This allows to repeat the operation in separate thread once again.
                    excelTemplate.ReleaseExcelResources();
                }

                #region Closing and Cleaning Up
                progress = -1;
                progressInfo.log = "Closing Excel and releasing resources. Please wait...";
                foreach (var excelTemplate in excelTemplateReports) excelTemplate.Dispose();
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                excelApplication.Quit();

                ReleaseObject(excelWorksheet);
                ReleaseObject(excelSheets);
                ReleaseObject(excelWorkbook);
                ReleaseObject(excelWorkbooks);
                ReleaseObject(excelApplication);

                progress = 100;
                progressInfo.log = "Ready";
                progressInfo.minValue = 0;
                progressInfo.maxValue = 100;
                progressInfo.value = 100;
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                #endregion
                
                return true;
            }

            #region Keeping things clean!
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, AppConfiguration.ApplicationName,
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                try
                {
                    excelWorkbook.Close(true, misValue, misValue);
                }
                catch { }
                try
                {
                    excelApplication.Quit();
                }
                catch { }

                ReleaseObject(excelWorksheet);
                ReleaseObject(excelSheets);
                ReleaseObject(excelWorkbook);
                ReleaseObject(excelWorkbooks);
                ReleaseObject(excelApplication);

                progress = -1;
                SAPRProgress progressInfo = new SAPRProgress();
                progressInfo.minValue = 0;
                progressInfo.maxValue = 100;
                progressInfo.log = "Error occured while trying to generate report";
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                return false;
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    try
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                    }
                    catch { }
                    try
                    {
                        excelApplication.Quit();
                    }
                    catch { }
                }

                ReleaseObject(excelWorksheet);
                ReleaseObject(excelSheets);
                ReleaseObject(excelWorkbook);
                ReleaseObject(excelWorkbooks);
                ReleaseObject(excelApplication);
            }
            #endregion
        }

        private bool GeneratePromiseSAPR_APR_COP14()
        {
            #region Function Constants and Variables
            const int ANC_REPORT_ID = 6;
            const int PCR_REPORT_ID = 8;
            const int MAT_REPORT_ID = 7;
            const int MC_REPORT_ID = 9;
            const int REGION_DISTRICT_FACILITY_COLUMN_COUNT = 3;

            OtherSystem targetSystem = OtherSystem.GetOtherSystem(OtherKnownSystem.PROMIS);

            int progress = -1;
            //string log = String.Empty;
            object misValue = System.Reflection.Missing.Value;
            completed = false;

            Excel.Application excelApplication = null;
            Excel.Workbooks excelWorkbooks = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Sheets excelSheets = null;
            Excel.Worksheet excelWorksheet = null;

            #endregion

            try
            {
                excelApplication = new Excel.Application();
                excelWorkbooks = excelApplication.Workbooks;
                SAPRProgress progressInfo = new SAPRProgress();
                DataSet allTargetFacilities = new DataSet();

                #region Getting list of target facilities from Excel file
                foreach (var excelTemplate in excelTemplateReports)
                {
                    string templatePath = String.Format(@"{0}\Resources\{1}", Application.StartupPath, excelTemplate.Filename);
                    string destinationFilename = String.Format(@"{0}\{1}", m_SelectedFilePath, excelTemplate.Filename);
                    if (!File.Exists(templatePath))
                    {
                        string message = "Template file for " + excelTemplate.Description + " is missing."
                            + "To continue please put the file in this location:\n"
                            + Application.StartupPath + @"\Resources\" + excelTemplate.Filename;
                        MessageBox.Show(message, AppConfiguration.ApplicationName,
                            MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return false;
                    }


                    System.IO.File.Copy(templatePath, destinationFilename, true);
                    excelTemplate.OutputFile = destinationFilename;

                    if (excelTemplate.MainTemplate)
                    {
                        excelWorkbook = excelWorkbooks.Open(destinationFilename, 0, false, 5,
                            "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                        if (excelTemplate.Sheets.Count == 1)
                        {
                            //int currentSheetIndex = 5;
                            excelSheets = excelWorkbook.Worksheets;
                            //excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheetIndex);     // 1-based index
                            excelWorksheet = (Excel.Worksheet)excelSheets[excelTemplate.Sheets[0].TemplateSheetName];

                            int startingRow = excelTemplate.Sheets[0].FirstRepetitiveRowCellIndex;
                            int startingColumn = excelTemplate.Sheets[0].FirstRepetitiveColumnCellIndex;
                            int rows = -1;
                            //int columns = startingColumn;
                            int columns = REGION_DISTRICT_FACILITY_COLUMN_COUNT;

                            progressInfo.log = "Retrieving list of target facilities from Excel file. Please wait...";
                            progressInfo.value = -1;
                            backgroundWorkerPromis.ReportProgress(progress, progressInfo);

                            DataTable targetFacilities = ReadExcelSheet(excelWorksheet, startingRow, startingColumn, rows, columns);
                            targetFacilities.TableName = String.Format("{0} - {1}", excelTemplate.TemplateID, excelTemplate.Name);
                            allTargetFacilities.Tables.Add(targetFacilities);

                            excelWorkbook.Close(false, misValue, misValue);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                            excelWorkbook = null;
                        }
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                }
                #endregion

                #region Indexing Excel sheet Values
                DataSet indicesSAPR = new DataSet();
                if (generateAPR && useSAPRAsBaseline)
                {
                    if (rtxtSelectedSAPRBaselineTemplates.Tag != null)
                    {
                        var selectedTemplates = (List<TemplateSelectorViewModel>)rtxtSelectedSAPRBaselineTemplates.Tag;
                        foreach (var t in selectedTemplates)
                        {
                            //ExcelTemplateReport excelTemplate = new ExcelTemplateReport(t.ExcelTemplateId);
                            ExcelTemplateReport excelTemplate = (from et in PromisTemplateSelector.ExcelTemplateReports
                                                                 where et.TemplateID == t.ExcelTemplateId
                                                                 select et).FirstOrDefault();
                            if (excelTemplate.MainTemplate)
                            {
                                if (File.Exists(t.TemplateLocation))
                                {
                                    excelWorkbook = excelWorkbooks.Open(t.TemplateLocation, 0, false, 5,
                                    "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                                    excelSheets = excelWorkbook.Worksheets;
                                    excelWorksheet = (Excel.Worksheet)excelSheets[excelTemplate.Sheets[0].TemplateSheetName];

                                    int startingRow = excelTemplate.Sheets[0].FirstRepetitiveRowCellIndex;
                                    int startingColumn = excelTemplate.Sheets[0].FirstRepetitiveColumnCellIndex;

                                    //DataTable indexing = ReadExcelSheet(excelWorksheet, startingRow, startingColumn, String.Empty);
                                    ExcelTemplateSheet sheetInfo = excelTemplate.Sheets[0];
                                    DataTable indexing = ReadPreviousExcelSheetData(excelWorksheet, sheetInfo, startingRow, startingColumn);
                                    excelWorkbook.Close(false, misValue, misValue);
                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                                    excelWorkbook = null;

                                    indexing.TableName = String.Format("{0} - Baseline: {1}", excelTemplate.TemplateID, excelTemplate.Name);
                                    indicesSAPR.Tables.Add(indexing);
                                }
                            }

                            if (backgroundWorkerPromis.CancellationPending)
                            {
                                excelWorkbook.Close(true, misValue, misValue);
                                excelApplication.Quit();

                                ReleaseObject(excelWorksheet);
                                ReleaseObject(excelSheets);
                                ReleaseObject(excelWorkbook);
                                ReleaseObject(excelWorkbooks);
                                ReleaseObject(excelApplication);
                                operationCanceled = true;
                                return false;
                            }
                        }
                    }
                }
                #endregion

                #region Get all facilities in particular district, district or nation
                //DataTable facilitiesDetails = null;
                DataSet summaryDataSet = new DataSet();

                progressInfo.log = "Retrieving records from database. Please wait...";
                progressInfo.value = -1;
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);

                progressInfo.log = "Retrieving list of facilities. Please wait...";
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                //facilitiesDetails = Utilities.GetAllFacilitiesDetails(true, true);
                //facilitiesDetails = Utilities.GetAllFacilitiesDetails_Alpha();
                //facilitiesDetails = Utilities.GetAllFacilitiesDetails();
                //facilitiesDetails = Utilities.GetAllFacilitiesDetailsByKnownSystem(targetSystem.Id);
                var facilitiesDetails = Utilities.GetOtherSystemsFacilityDetails(targetSystem.Id);
                if (backgroundWorkerPromis.CancellationPending)
                {
                    excelWorkbook.Close(true, misValue, misValue);
                    excelApplication.Quit();

                    ReleaseObject(excelWorksheet);
                    ReleaseObject(excelSheets);
                    ReleaseObject(excelWorkbook);
                    ReleaseObject(excelWorkbooks);
                    ReleaseObject(excelApplication);
                    operationCanceled = true;
                    return false;
                }
                #endregion

                #region Retrieving Report data
                #region Bulk Retrieval of data
                DateTime startDate = DateTime.Parse(String.Format("{0:0000}-{1:00}-01", sapr_startYear, sapr_startPeriod));
                DateTime endDate = DateTime.Parse(String.Format("{0:0000}-{1:00}-28", sapr_endYear, sapr_endPeriod));

                progress = 0;
                progressInfo.minValue = 0;
                if (sapr_useMaximumValues) progressInfo.maxValue = 9;
                else progressInfo.maxValue = 5;

                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading ANC Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable ANC_Reports = Utilities.GetBulkEachFacilityReport(ANC_REPORT_ID, startDate, endDate);

                progress++;
                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading PCR Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable PCR_Reports = Utilities.GetBulkEachFacilityReport(PCR_REPORT_ID, startDate, endDate);

                progress++;
                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading MAT Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable MAT_Reports = Utilities.GetBulkEachFacilityReport(MAT_REPORT_ID, startDate, endDate);

                progress++;
                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading MC Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable MC_Reports = Utilities.GetBulkEachFacilityReport(MC_REPORT_ID, startDate, endDate);

                progress++;
                progressInfo.value = progress;
                progressInfo.log = String.Format("Loading Testing and Counseling Reports: {0} {1} - {2} {3}, please wait...",
                    Utilities.GetMonthText(startDate.Month), startDate.Year, Utilities.GetMonthText(endDate.Month), endDate.Year);
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                DataTable TC_Reports = Utilities.GetTestingAndCounselingReport(quarters);

                DataTable Max_ANC_Reports = null;
                DataTable Max_PCR_Reports = null;
                DataTable Max_MAT_Reports = null;
                DataTable Max_MC_Reports = null;
                DataTable Max_TC_Reports = null;
                if (generateAPR && sapr_useMaximumValues)
                {
                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading ANC Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_ANC_Reports = Utilities.GetBulkEachFacilityReport(ANC_REPORT_ID, sapr_useMax_startDate, sapr_useMax_endDate);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading PCR Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_PCR_Reports = Utilities.GetBulkEachFacilityReport(PCR_REPORT_ID, sapr_useMax_startDate, sapr_useMax_endDate);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading MAT Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_MAT_Reports = Utilities.GetBulkEachFacilityReport(MAT_REPORT_ID, sapr_useMax_startDate, sapr_useMax_endDate);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading MC Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_MC_Reports = Utilities.GetBulkEachFacilityReport(MC_REPORT_ID, sapr_useMax_startDate, sapr_useMax_endDate);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format("Loading Testing and Counseling Reports: {0} {1} - {2} {3}, please wait...",
                        Utilities.GetMonthText(sapr_useMax_startDate.Month), sapr_useMax_startDate.Year,
                        Utilities.GetMonthText(sapr_useMax_endDate.Month), sapr_useMax_endDate.Year);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                    Max_TC_Reports = Utilities.GetTestingAndCounselingReport(max_quarters);
                }
                #endregion

                //int facilityCount = facilitiesDetails.Rows.Count;
                int facilityCount = facilitiesDetails.Count;
                int facilityIndex = 0;
                int totalWork = facilityCount;
                progress = 0;
                progressInfo.minValue = 0;
                progressInfo.maxValue = totalWork;
                string template = "{0} in {1} | {2} ({3} of {4}): Loading Report data, please wait...";
                List<DataSet> facilitiesDataSet = new List<DataSet>();
                //foreach (DataRow row in facilitiesDetails.Rows)
                foreach (var fd in facilitiesDetails)
                {
                    facilityIndex++;

                    string internalFacilityCode = fd.InternalFacilityCode;
                    string internalFacilityName = fd.InternalFacilityName;
                    string internalDistrictName = fd.InternalDistrictName;
                    string internalRegionName = fd.InternalRegionName;

                    string facilityCode = fd.FacilityCode;
                    string facilityName = fd.FacilityName;
                    string facilityType = fd.FacilityType.ToString();
                    string provideDBS = fd.ProvideDBS.ToString();
                    string districtName = fd.DistrictName;
                    string regionName = fd.RegionName;

                    DataTable max_DataTemplate = new DataTable();
                    DataColumn c = new DataColumn("Data Description", Type.GetType("System.String"));
                    max_DataTemplate.Columns.Add(c);
                    c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                    max_DataTemplate.Columns.Add(c);

                    progress++;
                    progressInfo.value = progress;
                    progressInfo.log = String.Format(template, internalDistrictName, internalRegionName,
                        internalFacilityName, facilityIndex, facilityCount);
                    backgroundWorkerPromis.ReportProgress(progress, progressInfo);

                    #region ANC
                    DataSet facilityDataset = new DataSet();
                    DataTable data = null; // this table consists of two column Data Description and ReportData
                    DataTable max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < ANC_Reports.Rows.Count; k++)
                        {
                            DataRow r = ANC_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < ANC_Reports.Columns.Count; i++)
                                {
                                    string columnName = ANC_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                ANC_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_ANC_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_ANC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_ANC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_ANC_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_ANC_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_ANC_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_ANC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_ANC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_ANC_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch //This is just the old fall back, I don't expect it to be used
                    {
                        do
                        {
                            try
                            {
                                data = Utilities.GetFacilityReport(internalFacilityCode,
                                    ANC_REPORT_ID, sapr_startYear, sapr_startPeriod, sapr_endYear,
                                    sapr_endPeriod, true);
                                break;
                            }
                            catch (Exception ex)
                            {
                                DialogResult response = MessageBox.Show(this, ex.Message,
                                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                            }
                        } while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.ANC_TABLE;
                    if (sapr_useMaximumValues) max_Data.TableName = MAX_ANC;

                    #region This is the Clement special request
                    DataTable anc = data;
                    DataRow anc04Row = anc.Rows[3];
                    DataRow anc08Row = anc.Rows[7];
                    int anc04 = 0;
                    int anc08 = 0;
                    Int32.TryParse(anc04Row[0].ToString(), out anc04);
                    Int32.TryParse(anc08Row[0].ToString(), out anc08);
                    if (anc08 < anc04)
                    {
                        DataRow anc06Row = anc.Rows[5];
                        int anc06 = 0;
                        Int32.TryParse(anc06Row[0].ToString(), out anc06);
                        if ((anc04 == anc06) && (anc08 == 0))
                        {
                            DataRow anc07Row = anc.Rows[6];
                            int anc07 = 0;
                            Int32.TryParse(anc07Row[0].ToString(), out anc07);

                            anc06Row[0] = anc07;
                            anc07Row[0] = anc08;
                            anc08Row[0] = anc06;
                        }
                    }

                    if (sapr_useMaximumValues)
                    {
                        anc = max_Data;
                        anc04Row = anc.Rows[3];
                        anc08Row = anc.Rows[7];
                        anc04 = 0;
                        anc08 = 0;
                        Int32.TryParse(anc04Row[0].ToString(), out anc04);
                        Int32.TryParse(anc08Row[0].ToString(), out anc08);
                        if (anc08 < anc04)
                        {
                            DataRow anc06Row = anc.Rows[5];
                            int anc06 = 0;
                            Int32.TryParse(anc06Row[0].ToString(), out anc06);
                            if ((anc04 == anc06) && (anc08 == 0))
                            {
                                DataRow anc07Row = anc.Rows[6];
                                int anc07 = 0;
                                Int32.TryParse(anc07Row[0].ToString(), out anc07);

                                anc06Row[0] = anc07;
                                anc07Row[0] = anc08;
                                anc08Row[0] = anc06;
                            }
                        }
                    }
                    #endregion

                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues) facilityDataset.Tables.Add(max_Data);

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region PCR
                    data = null;
                    max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < PCR_Reports.Rows.Count; k++)
                        {
                            DataRow r = PCR_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < PCR_Reports.Columns.Count; i++)
                                {
                                    string columnName = PCR_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                PCR_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_PCR_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_PCR_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_PCR_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_PCR_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_PCR_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_PCR_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_PCR_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_PCR_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_PCR_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch
                    {
                        do
                        {
                            try
                            {
                                data = Utilities.GetFacilityReport(internalFacilityCode,
                                    PCR_REPORT_ID, sapr_startYear, sapr_startPeriod, sapr_endYear,
                                    sapr_endPeriod, true);
                                break;
                            }
                            catch (Exception ex)
                            {
                                DialogResult response = MessageBox.Show(this, ex.Message,
                                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                            }
                        } while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.PCR_TABLE;
                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues)
                    {
                        max_Data.TableName = MAX_PCR;
                        facilityDataset.Tables.Add(max_Data);
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region MAT
                    data = null;
                    max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < MAT_Reports.Rows.Count; k++)
                        {
                            DataRow r = MAT_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < MAT_Reports.Columns.Count; i++)
                                {
                                    string columnName = MAT_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                MAT_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_MAT_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_MAT_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_MAT_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_MAT_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_MAT_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_MAT_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_MAT_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_MAT_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_MAT_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch
                    {
                        do
                        {
                            try
                            {
                                data = Utilities.GetFacilityReport(internalFacilityCode, MAT_REPORT_ID,
                                    sapr_startYear, sapr_startPeriod, sapr_endYear, sapr_endPeriod, true);
                                break;
                            }
                            catch (Exception ex)
                            {
                                DialogResult response = MessageBox.Show(this, ex.Message,
                                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                            }
                        } while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.MAT_TABLE;
                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues)
                    {
                        max_Data.TableName = MAX_MAT;
                        facilityDataset.Tables.Add(max_Data);
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region MC
                    data = null;
                    max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < MC_Reports.Rows.Count; k++)
                        {
                            DataRow r = MC_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < MC_Reports.Columns.Count; i++)
                                {
                                    string columnName = MC_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                MC_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_MC_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_MC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_MC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_MC_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_MC_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_MC_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_MC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_MC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_MC_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch
                    {
                        do
                        {
                            try
                            {
                                data = Utilities.GetFacilityReport(internalFacilityCode, MC_REPORT_ID,
                                    sapr_startYear, sapr_startPeriod, sapr_endYear, sapr_endPeriod, true);
                                break;
                            }
                            catch (Exception ex)
                            {
                                DialogResult response = MessageBox.Show(this, ex.Message,
                                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                            }
                        } while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.MC_TABLE;
                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues)
                    {
                        max_Data.TableName = MAX_MC;
                        facilityDataset.Tables.Add(max_Data);
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region TC
                    data = null;
                    max_Data = null;

                    try
                    {
                        int nextFirstTrialIndex = 0;
                        bool nextFirtTrialIndexFound = false;
                        #region Processing Bulk data and put into more suitable format
                        for (int k = 0; k < TC_Reports.Rows.Count; k++)
                        {
                            DataRow r = TC_Reports.Rows[k];

                            string code = r["FacCode"].ToString();
                            if (code == internalFacilityCode)
                            {
                                data = new DataTable();
                                c = new DataColumn("Data Description", Type.GetType("System.String"));
                                data.Columns.Add(c);
                                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                data.Columns.Add(c);
                                nextFirstTrialIndex = k;
                                nextFirtTrialIndexFound = true;

                                for (int i = 1; i < TC_Reports.Columns.Count; i++)
                                {
                                    string columnName = TC_Reports.Columns[i].ColumnName;
                                    int reportData = 0;
                                    Int32.TryParse(r[columnName].ToString(), out reportData);

                                    DataRow indicatorDataRow = data.NewRow();
                                    indicatorDataRow[0] = columnName;
                                    indicatorDataRow[1] = reportData;
                                    data.Rows.Add(indicatorDataRow);
                                }

                                TC_Reports.Rows.Remove(r); //Minimize repetitions
                                break;
                            }
                        }
                        #endregion

                        #region Indexing data for Picking maximum values
                        if (sapr_useMaximumValues)
                        {
                            bool found = false;
                            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
                            {
                                try
                                {
                                    DataRow r = Max_TC_Reports.Rows[nextFirstTrialIndex];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        found = true;
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_TC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_TC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_TC_Reports.Rows.Remove(r); //Minimize repetitions
                                    }
                                }
                                catch
                                {
                                    found = false;
                                }
                            }

                            if (!found)
                            {
                                for (int k = 0; k < Max_TC_Reports.Rows.Count; k++)
                                {
                                    DataRow r = Max_TC_Reports.Rows[k];
                                    string code = r["FacCode"].ToString();
                                    if (code == internalFacilityCode)
                                    {
                                        max_Data = new DataTable();
                                        c = new DataColumn("Data Description", Type.GetType("System.String"));
                                        max_Data.Columns.Add(c);
                                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
                                        max_Data.Columns.Add(c);

                                        for (int i = 1; i < Max_TC_Reports.Columns.Count; i++)
                                        {
                                            string columnName = Max_TC_Reports.Columns[i].ColumnName;
                                            int reportData = 0;
                                            Int32.TryParse(r[columnName].ToString(), out reportData);

                                            DataRow indicatorDataRow = max_Data.NewRow();
                                            indicatorDataRow[0] = columnName;
                                            indicatorDataRow[1] = reportData;
                                            max_Data.Rows.Add(indicatorDataRow);
                                        }

                                        Max_TC_Reports.Rows.Remove(r); //Minimize repetitions
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                    #region This is just the old fall back, I don't expect it to be used
                    catch
                    {
                        //do
                        //{
                        //    try
                        //    {
                        //        data = Utilities.GetFacilityReport(internalFacilityCode, MC_REPORT_ID,
                        //            sapr_startYear, sapr_startPeriod, sapr_endYear, sapr_endPeriod, true);
                        //        break;
                        //    }
                        //    catch (Exception ex)
                        //    {
                        //        DialogResult response = MessageBox.Show(this, ex.Message,
                        //            String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
                        //            MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
                        //        if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
                        //    }
                        //} while (true);
                    }
                    #endregion

                    data.TableName = ExcelTemplateReport.TC_TABLE;
                    facilityDataset.Tables.Add(data);
                    if (sapr_useMaximumValues)
                    {
                        max_Data.TableName = MAX_TC;
                        facilityDataset.Tables.Add(max_Data);
                    }

                    if (backgroundWorkerPromis.CancellationPending)
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                        excelApplication.Quit();

                        ReleaseObject(excelWorksheet);
                        ReleaseObject(excelSheets);
                        ReleaseObject(excelWorkbook);
                        ReleaseObject(excelWorkbooks);
                        ReleaseObject(excelApplication);
                        operationCanceled = true;
                        return false;
                    }
                    #endregion

                    #region Generating Facility info table
                    DataTable facilityInfo = new DataTable();
                    DataColumn column = new DataColumn(FACCODE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(FACILITY_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DISTRICT_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(REGION_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(FACILITY_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(PROVIDEDBS, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(HOSPITAL, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DISPENSARY, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(HEALTH_CENTRE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(PMTCTSITE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(ANCSERVICES, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.RCH_FACILITY, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);

                    column = new DataColumn(INTERNAL_FACILITY_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(INTERNAL_DISTRICT_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(INTERNAL_REGION_NAME, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);

                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.HTC_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.Care_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.FN_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TB_HIV_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TX_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.PMTCT_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.HRH_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.LAB_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.VMMC_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.GPY_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.KP_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.OVC_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.FPINT_SERVICE_TYPE, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);

                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_HTC, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_TREATMENT, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_CARE_AND_SUPPORT, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_FOOD_AND_NUTRITION, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_PMTCT, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_TB_HIV, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_VMMC, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_KEY_POP_PREVENTION, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_PHDP_FAMILY_PLANNING, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.DSD_LAB, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);

                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_HTC, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_TREATMENT, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_CARE_AND_SUPPORT, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_FOOD_AND_NUTRITION, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_PMTCT, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_TB_HIV, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_VMMC, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_KEY_POP_PREVENTION, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_PHDP_FAMILY_PLANNING, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);
                    column = new DataColumn(DataModels.Models.OtherSystemsFacilityDetail.TA_LAB, Type.GetType("System.String"));
                    facilityInfo.Columns.Add(column);

                    string[] values = { 
										  facilityCode, facilityName, districtName, regionName, 
										  facilityType, provideDBS, String.Empty, String.Empty, String.Empty, 
                                          useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE, "TRUE",
                                          useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE,
                                          internalFacilityName, internalDistrictName, internalRegionName,
                                          
                                          fd.HTC_ServiceType, fd.Care_ServiceType, fd.FN_ServiceType,
                                          fd.TB_HIV_ServiceType, fd.TX_ServiceType, fd.PMTCT_ServiceType,
                                          fd.HRH_ServiceType, fd.LAB_ServiceType, fd.VMMC_ServiceType,
                                          fd.GPY_ServiceType, fd.KP_ServiceType, fd.OVC_ServiceType,
                                          fd.FPINT_ServiceType,

                                          fd.HTC_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.TX_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.Care_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.FN_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.PMTCT_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.TB_HIV_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.VMMC_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.KP_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.FPINT_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.LAB_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.DSD ? BOOLEAN_TRUE : BOOLEAN_FALSE,

                                          fd.HTC_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.TX_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.Care_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.FN_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.PMTCT_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.TB_HIV_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.VMMC_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.KP_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.FPINT_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE,
                                          fd.LAB_ServiceType == DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY ? BOOLEAN_TRUE : BOOLEAN_FALSE

									  };
                    facilityInfo.Rows.Add(values);
                    facilityInfo.TableName = ExcelTemplateReport.FACILITY_INFO_TABLE;
                    #endregion
                    facilityDataset.Tables.Add(facilityInfo);

                    facilitiesDataSet.Add(facilityDataset);
                }
                #endregion

                // From this point on we will use index object which should contain all SAPR
                // data from all facilities from all input SAPR files.
                DataTable indexSAPR = null;
                if (generateAPR && useSAPRAsBaseline)
                {
                    indexSAPR = indicesSAPR.Tables[0].Clone();
                    foreach (DataTable table in indicesSAPR.Tables) indexSAPR.Merge(table);

                    // Modifying the table to match the target format based on the their relationship
                    // By now the table columns are named according to their column index.
                    // e.g. is the full address is AB29, then the column will be named 28
                    // So by using association objects will match these columns to reflect the target template
                    List<DataModels.Models.ExcelTemplateCellAssociation> cellAssociations = null;
                    int sheetId = excelTemplateReports[0].Sheets[0].SheetID;
                    cellAssociations = Utilities.GetExcelTemplateSheetCellAssociations(sheetId);

                    // The first three columns represents region, district and facility name
                    // we are not after those columns!
                    // If the column is not the list , we simply remove it
                    List<string> columnsToBeRemoved = new List<string>();
                    List<string> columnsToBeAdded = new List<string>();
                    for (int i = 3; i < indexSAPR.Columns.Count; i++)
                    {
                        string column = indexSAPR.Columns[i].ColumnName;
                        bool removeColumn = true;
                        foreach (var association in cellAssociations)
                        {                            
                            if (column == association.Cell2.CellAddress)
                            {
                                if (association.IsNumeric) removeColumn = false;
                                else removeColumn = true;
                                break;
                            }
                        }

                        if (removeColumn) columnsToBeRemoved.Add(column);
                    }

                    // Removing extra columns and re-name the column name be based on the column index
                    foreach (var column in columnsToBeRemoved) indexSAPR.Columns.Remove(column);
                    foreach (var association in cellAssociations)
                    {
                        string currentColumnName = association.Cell2.CellAddress;
                        string newColumnName = ExcelTemplateReport.GetColumnNumber(association.Cell1.CellAddress).ToString();
                        indexSAPR.Columns[currentColumnName].ColumnName = newColumnName;
                    }
                }

                foreach (var excelTemplate in excelTemplateReports)
                {
                    if (excelTemplate.MainTemplate)
                    {
                        #region Retrieving TargetFacilities DataTable
                        string targetTableName = String.Format("{0} - {1}", excelTemplate.TemplateID, excelTemplate.Name);
                        DataTable targetFacilities = null;
                        foreach (DataTable table in allTargetFacilities.Tables)
                        {
                            if (table.TableName == targetTableName)
                            {
                                targetFacilities = table;
                                break;
                            }
                        }
                        #endregion

                        int columnOffset = 0;
                        int rowOffset = 0;
                        template = @"Writing to Facility data to Excel File: {2} [{0} of {1}]";
                        facilityIndex = 0;
                        facilityCount = targetFacilities.Rows.Count;

                        progress = 0;
                        progressInfo.minValue = 0;
                        progressInfo.maxValue = facilityCount;

                        #region Processing Every Facility in the Template
                        foreach (DataRow targetFacility in targetFacilities.Rows)
                        {
                            facilityIndex++;
                            progressInfo.log = String.Format(template, facilityIndex, facilityCount, excelTemplate.Filename);
                            progressInfo.value = facilityIndex;
                            backgroundWorkerPromis.ReportProgress(facilityIndex, progressInfo);

                            string targetRegion = targetFacility[0].ToString();
                            string targetDistrict = targetFacility[1].ToString();
                            string targetFacilityName = targetFacility[2].ToString();

                            FindAndWriteFacilityData(facilitiesDataSet, targetRegion, targetDistrict, targetFacilityName,
                                excelTemplate, indexSAPR, rowOffset, columnOffset);
                            rowOffset++;

                            //excelWorkbook.Save();
                            if (backgroundWorkerPromis.CancellationPending)
                            {
                                excelWorkbook.Close(true, misValue, misValue);
                                excelApplication.Quit();

                                ReleaseObject(excelWorksheet);
                                ReleaseObject(excelSheets);
                                ReleaseObject(excelWorkbook);
                                ReleaseObject(excelWorkbooks);
                                ReleaseObject(excelApplication);
                                operationCanceled = true;
                                return false;
                            }
                        }
                        #endregion
                    }
                    else
                    {
                        WriteDataForMissingFacilities(facilitiesDataSet, excelTemplate);
                    }

                    // Release some excel resources related to ExcelTemplateReport object.
                    // This allows to repeat the operation in separate thread once again.
                    excelTemplate.ReleaseExcelResources();
                }

                #region Closing and Cleaning Up
                progress = -1;
                progressInfo.log = "Closing Excel and releasing resources. Please wait...";
                foreach (var excelTemplate in excelTemplateReports) excelTemplate.Dispose();
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                excelApplication.Quit();

                ReleaseObject(excelWorksheet);
                ReleaseObject(excelSheets);
                ReleaseObject(excelWorkbook);
                ReleaseObject(excelWorkbooks);
                ReleaseObject(excelApplication);

                progress = 100;
                progressInfo.log = "Ready";
                progressInfo.minValue = 0;
                progressInfo.maxValue = 100;
                progressInfo.value = 100;
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                #endregion

                return true;
            }

            #region Keeping things clean!
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, AppConfiguration.ApplicationName,
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                try
                {
                    excelWorkbook.Close(true, misValue, misValue);
                }
                catch { }
                try
                {
                    excelApplication.Quit();
                }
                catch { }

                ReleaseObject(excelWorksheet);
                ReleaseObject(excelSheets);
                ReleaseObject(excelWorkbook);
                ReleaseObject(excelWorkbooks);
                ReleaseObject(excelApplication);

                progress = -1;
                SAPRProgress progressInfo = new SAPRProgress();
                progressInfo.minValue = 0;
                progressInfo.maxValue = 100;
                progressInfo.log = "Error occured while trying to generate report";
                backgroundWorkerPromis.ReportProgress(progress, progressInfo);
                return false;
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    try
                    {
                        excelWorkbook.Close(true, misValue, misValue);
                    }
                    catch { }
                    try
                    {
                        excelApplication.Quit();
                    }
                    catch { }
                }

                ReleaseObject(excelWorksheet);
                ReleaseObject(excelSheets);
                ReleaseObject(excelWorkbook);
                ReleaseObject(excelWorkbooks);
                ReleaseObject(excelApplication);
            }
            #endregion
        }

        private void FindAndWriteFacilityData(List<DataSet> facilitiesDataSet, string targetRegion, 
            string targetDistrict, string targetFacilityName, ExcelTemplateReport excelTemplate, 
            DataTable indexingPreviousData, int rowOffset, int columnOffset)
        {
            foreach (DataSet facilityDataSet in facilitiesDataSet)
            {
                DataTable facilityInfo = facilityDataSet.Tables[ExcelTemplateReport.FACILITY_INFO_TABLE];
                DataRow r = facilityInfo.Rows[0];
                string name = r[FACILITY_NAME].ToString();
                string district = r[DISTRICT_NAME].ToString();
                string region = r[REGION_NAME].ToString();
                if ((region == targetRegion) && (district == targetDistrict) && (name == targetFacilityName))
                {
                    //string code = r["FACCODE"].ToString();
                    bool provideDBS = false;
                    Boolean.TryParse(r[PROVIDEDBS].ToString(), out provideDBS);

                    #region Finding Whether they provide DBS
                    if (provideDBS)
                    {
                        string type = r[FACILITY_TYPE].ToString();
                        int iType = FACILITY_TYPE_DISPENSARY;
                        Int32.TryParse(type, out iType);

                        if (iType == FACILITY_TYPE_DISPENSARY) r[DISPENSARY] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                        if (iType == FACILITY_TYPE_HEALTH_CENTRE) r[HEALTH_CENTRE] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                        if (iType == FACILITY_TYPE_HOSPITAL) r[HOSPITAL] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                    }
                    else // finding DBS using another means i.e. MAT08 > 0
                    {
                        try
                        {
                            DataTable mat = facilityDataSet.Tables[ExcelTemplateReport.MAT_TABLE];
                            DataRow mat08Row = mat.Rows[7];
                            string key = mat08Row[0].ToString();
                            string value = mat08Row[1].ToString();
                            if (key == "MAT08")
                            {
                                int iValue = 0;
                                Int32.TryParse(value, out iValue);
                                if (iValue > 0)
                                {
                                    r[PROVIDEDBS] = true;
                                    string type = r[FACILITY_TYPE].ToString();
                                    int iType = FACILITY_TYPE_DISPENSARY;
                                    Int32.TryParse(type, out iType);

                                    if (iType == FACILITY_TYPE_DISPENSARY) r[DISPENSARY] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE; ;
                                    if (iType == FACILITY_TYPE_HEALTH_CENTRE) r[HEALTH_CENTRE] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE; ;
                                    if (iType == FACILITY_TYPE_HOSPITAL) r[HOSPITAL] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE; ;
                                }
                            }
                        }
                        catch { }
                    }
                    #endregion

                    excelTemplate.DataSource = facilityDataSet;
                    excelTemplate.SimplifyDatasetFormat(true);

                    #region Fine-tuning the APR
                    // Adding data from baseline sheets
                    if (generateAPR && sapr_useBaselineData)
                    {
                        Dictionary<int, int> dIndex1 = new Dictionary<int, int>();
                        foreach (DataRow x in indexingPreviousData.Rows)
                        {
                            string xName = x[FACILITY_NAME].ToString();
                            string xDistrict = x[DISTRICT_NAME].ToString();
                            string xRegion = x[REGION_NAME].ToString();
                            if ((xName == targetFacilityName) && (xRegion == targetRegion))
                            {
                                int startingColumn = 3;
                                // Now generate the 
                                for (int y = startingColumn; y < indexingPreviousData.Columns.Count; y++)
                                {
                                    string sColumnName = indexingPreviousData.Columns[y].ColumnName;
                                    int iColumnName = Int32.Parse(sColumnName);
                                    dIndex1[iColumnName] = Int32.Parse(x[sColumnName].ToString());
                                }
                                indexingPreviousData.Rows.Remove(x);
                                break;
                            }
                        }

                        foreach (var z in excelTemplate.Sheets)
                        {
                            z.AddValueToCells(dIndex1);
                            if (generateAPR && sapr_useMaximumValues)
                            {
                                foreach (var c in z.Cells)
                                {
                                    int _6monthsValue = 0;
                                    int _12mothsValue = 0;
                                    int _6monthsValueSAPR = 0;
                                    bool isNumerical = false;
                                    isNumerical = Int32.TryParse(c.Value, out _6monthsValue);
                                    if (isNumerical)
                                    {
                                        _6monthsValue = c.GetValue(facilityDataSet, 
                                            ExcelTemplateReport.ANC_TABLE,
                                            ExcelTemplateReport.PCR_TABLE, 
                                            ExcelTemplateReport.MAT_TABLE, 
                                            ExcelTemplateReport.MC_TABLE,
                                            ExcelTemplateReport.TC_TABLE);
                                        _12mothsValue = c.GetValue(facilityDataSet, MAX_ANC, MAX_PCR, MAX_MAT, MAX_MC, MAX_TC);
                                        _6monthsValueSAPR = Int32.Parse(c.Value);

                                        if (_12mothsValue > _6monthsValueSAPR)
                                        {
                                            int extraValue = _12mothsValue - _6monthsValueSAPR;
                                            int newValue = Int32.Parse(c.Value) + extraValue;
                                            c.Value = newValue.ToString();
                                        }
                                    }
                                }
                            }
                        }
                    }
                    #endregion

                    //excelTemplate.WriteDataToExcelSheet(excelWorksheet, INDICATOR_DATA_ENTRY, columnOffset, rowOffset, false);
                    excelTemplate.WriteDataToExcelSheet(excelTemplate.Sheets[0].TemplateSheetName, columnOffset, rowOffset, false, r);
                    facilitiesDataSet.Remove(facilityDataSet);  //To reduce looping already processed data
                    break;
                }
            }
        }

        private void WriteDataForMissingFacilities(List<DataSet> facilitiesDataSet, ExcelTemplateReport excelTemplate)
        {
            int columnOffset = 0;
            int rowOffset = 0;
            string template = @"Writing to Facility data to Excel File: {2} [{0} of {1}]";
            int facilityIndex = 0;
            int facilityCount = facilitiesDataSet.Count;

            SAPRProgress progressInfo = new SAPRProgress();
            progressInfo.minValue = 0;
            progressInfo.maxValue = facilityCount;

            foreach (DataSet facilityDataSet in facilitiesDataSet)
            {
                facilityIndex++;
                progressInfo.log = String.Format(template, facilityIndex, facilityCount, excelTemplate.Filename);
                progressInfo.value = facilityIndex;
                backgroundWorkerPromis.ReportProgress(facilityIndex, progressInfo);

                DataTable facilityInfo = facilityDataSet.Tables[ExcelTemplateReport.FACILITY_INFO_TABLE];
                DataRow r = facilityInfo.Rows[0];
                string name = r[FACILITY_NAME].ToString();
                string district = r[DISTRICT_NAME].ToString();
                string region = r[REGION_NAME].ToString();

                bool provideDBS = false;
                Boolean.TryParse(r[PROVIDEDBS].ToString(), out provideDBS);
                if (provideDBS)
                {
                    r[PMTCTSITE] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE; 
                    string type = r[FACILITY_TYPE].ToString();
                    int iType = FACILITY_TYPE_DISPENSARY;
                    Int32.TryParse(type, out iType);

                    if (iType == FACILITY_TYPE_DISPENSARY) r[DISPENSARY] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                    if (iType == FACILITY_TYPE_HEALTH_CENTRE) r[HEALTH_CENTRE] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                    if (iType == FACILITY_TYPE_HOSPITAL) r[HOSPITAL] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                }
                else // finding DBS using another means i.e. MAT08 > 0
                {
                    try
                    {
                        DataTable mat = facilityDataSet.Tables[ExcelTemplateReport.MAT_TABLE];
                        DataRow mat08Row = mat.Rows[7];
                        string key = mat08Row[0].ToString();
                        string value = mat08Row[1].ToString();
                        if (key == "MAT08")
                        {
                            int iValue = 0;
                            Int32.TryParse(value, out iValue);
                            if (iValue > 0)
                            {
                                r[PROVIDEDBS] = true;
                                string type = r[FACILITY_TYPE].ToString();
                                int iType = FACILITY_TYPE_DISPENSARY;
                                Int32.TryParse(type, out iType);

                                if (iType == FACILITY_TYPE_DISPENSARY) r[DISPENSARY] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                                if (iType == FACILITY_TYPE_HEALTH_CENTRE) r[HEALTH_CENTRE] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                                if (iType == FACILITY_TYPE_HOSPITAL) r[HOSPITAL] = useDigitForBoolean ? DIGIT_TRUE : BOOLEAN_TRUE;
                            }
                        }
                    }
                    catch { }
                }

                excelTemplate.DataSource = facilityDataSet;
                excelTemplate.WriteDataToExcelSheet(excelTemplate.Sheets[0].TemplateSheetName, columnOffset, rowOffset, true, r);
                //excelWorkbook.Save();
                rowOffset++;
            }
        }

        private void DisplayTemplatesInRichText(RichTextBox richTextBox, List<TemplateSelectorViewModel> templates)
        {
            // Clear all text from the RichTextBox.
            richTextBox.Clear();

            // Set the font for the opening text to a larger Arial font;
            //richTextBox.SelectionFont = new Font("Arial", 16);

            // Assign the introduction text to the RichTextBox control.
            //richTextBox.SelectedText = "The following is a list of bulleted items:" + "\n";

            // Set the Font for the first item to a smaller size Arial font.
            //richTextBox.SelectionFont = new Font("Arial", 12);

            // Specify that the following items are to be added to a bulled list.
            richTextBox.SelectionBullet = true;

            foreach (var t in templates)
            {
                richTextBox.SelectedText = t.DisplayName + "\n";
            }

            // Set the color of the item text.
            //richTextBox.SelectionColor = Color.Red;

            // Assign the text to the bulleted item.
            //richTextBox.SelectedText = "Apples" + "\n";

            // Apply same font since font settings do not carry to next line.
            //richTextBox.SelectionFont = new Font("Arial", 12);
            //richTextBox.SelectionColor = Color.Orange;
            //richTextBox.SelectedText = "Oranges" + "\n";

            //richTextBox.SelectionFont = new Font("Arial", 12);
            //richTextBox.SelectionColor = Color.Purple;
            //richTextBox.SelectedText = "Grapes" + "\n";

            // End the bulleted list.
            richTextBox.SelectionBullet = false;

            // Specify the font size and string for text displayed below bulleted list.
            //richTextBox.SelectionFont = new Font("Arial", 16);
            //richTextBox.SelectedText = "Bulleted Text Completed!";
        }

        // TODO: Un-comment to continue building the method which intendend for compacting repetitive lines of codes
        //private static void Method(DataTable PCR_Reports, DataTable Max_PCR_Reports, string internalFacilityCode)
        //{
        //    #region PCR
        //    DataTable data = null;
        //    DataTable max_Data = null;

        //    try
        //    {
        //        int nextFirstTrialIndex = 0;
        //        bool nextFirtTrialIndexFound = false;
        //        #region Processing Bulk data and put into more suitable format
        //        for (int k = 0; k < PCR_Reports.Rows.Count; k++)
        //        {
        //            DataRow r = PCR_Reports.Rows[k];

        //            string code = r["FacCode"].ToString();
        //            if (code == internalFacilityCode)
        //            {
        //                data = new DataTable();
        //                DataColumn c = new DataColumn("Data Description", Type.GetType("System.String"));
        //                data.Columns.Add(c);
        //                c = new DataColumn("ReportData", Type.GetType("System.Int32"));
        //                data.Columns.Add(c);
        //                nextFirstTrialIndex = k;
        //                nextFirtTrialIndexFound = true;

        //                for (int i = 1; i < PCR_Reports.Columns.Count; i++)
        //                {
        //                    string columnName = PCR_Reports.Columns[i].ColumnName;
        //                    int reportData = 0;
        //                    Int32.TryParse(r[columnName].ToString(), out reportData);

        //                    DataRow indicatorDataRow = data.NewRow();
        //                    indicatorDataRow[0] = columnName;
        //                    indicatorDataRow[1] = reportData;
        //                    data.Rows.Add(indicatorDataRow);
        //                }

        //                PCR_Reports.Rows.Remove(r); //Minimize repetitions
        //                break;
        //            }
        //        }
        //        #endregion

        //        #region Indexing data for Picking maximum values
        //        if (sapr_useMaximumValues)
        //        {
        //            bool found = false;
        //            if (nextFirtTrialIndexFound) // This is for quick search, in case the table have same facilities in the same order.
        //            {
        //                try
        //                {
        //                    DataRow r = Max_PCR_Reports.Rows[nextFirstTrialIndex];
        //                    string code = r["FacCode"].ToString();
        //                    if (code == internalFacilityCode)
        //                    {
        //                        found = true;
        //                        max_Data = new DataTable();
        //                        DataColumn c = new DataColumn("Data Description", Type.GetType("System.String"));
        //                        max_Data.Columns.Add(c);
        //                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
        //                        max_Data.Columns.Add(c);

        //                        for (int i = 1; i < Max_PCR_Reports.Columns.Count; i++)
        //                        {
        //                            string columnName = Max_PCR_Reports.Columns[i].ColumnName;
        //                            int reportData = 0;
        //                            Int32.TryParse(r[columnName].ToString(), out reportData);

        //                            DataRow indicatorDataRow = max_Data.NewRow();
        //                            indicatorDataRow[0] = columnName;
        //                            indicatorDataRow[1] = reportData;
        //                            max_Data.Rows.Add(indicatorDataRow);
        //                        }

        //                        Max_PCR_Reports.Rows.Remove(r); //Minimize repetitions
        //                    }
        //                }
        //                catch
        //                {
        //                    found = false;
        //                }
        //            }

        //            if (!found)
        //            {
        //                for (int k = 0; k < Max_PCR_Reports.Rows.Count; k++)
        //                {
        //                    DataRow r = Max_PCR_Reports.Rows[k];
        //                    string code = r["FacCode"].ToString();
        //                    if (code == internalFacilityCode)
        //                    {
        //                        max_Data = new DataTable();
        //                        DataColumn c = new DataColumn("Data Description", Type.GetType("System.String"));
        //                        max_Data.Columns.Add(c);
        //                        c = new DataColumn("ReportData", Type.GetType("System.Int32"));
        //                        max_Data.Columns.Add(c);

        //                        for (int i = 1; i < Max_PCR_Reports.Columns.Count; i++)
        //                        {
        //                            string columnName = Max_PCR_Reports.Columns[i].ColumnName;
        //                            int reportData = 0;
        //                            Int32.TryParse(r[columnName].ToString(), out reportData);

        //                            DataRow indicatorDataRow = max_Data.NewRow();
        //                            indicatorDataRow[0] = columnName;
        //                            indicatorDataRow[1] = reportData;
        //                            max_Data.Rows.Add(indicatorDataRow);
        //                        }

        //                        Max_PCR_Reports.Rows.Remove(r); //Minimize repetitions
        //                        break;
        //                    }
        //                }
        //            }
        //        }
        //        #endregion
        //    }
        //    #region This is just the old fall back, I don't expect it to be used
        //    catch
        //    {
        //        do
        //        {
        //            try
        //            {
        //                data = Utilities.GetFacilityReport(internalFacilityCode,
        //                    PCR_REPORT_ID, sapr_startYear, sapr_startPeriod, sapr_endYear,
        //                    sapr_endPeriod, true);
        //                break;
        //            }
        //            catch (Exception ex)
        //            {
        //                DialogResult response = MessageBox.Show(this, ex.Message,
        //                    String.Format("{0} - Error retrieving data", AppConfiguration.ApplicationName),
        //                    MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
        //                if (response == System.Windows.Forms.DialogResult.Cancel) throw ex;
        //            }
        //        } while (true);
        //    }
        //    #endregion
        //    #endregion
        //}
        #endregion

        #region Event Handlers
        private void rdoGenerateSAPR_CheckedChanged(object sender, EventArgs e)
        {
            gboSAPR.Enabled = rdoGenerateSAPR.Checked;
            generateSAPR = rdoGenerateSAPR.Checked;
        }

        private void rdoGenerateAPR_CheckedChanged(object sender, EventArgs e)
        {
            gboAPR.Enabled = rdoGenerateAPR.Checked;
            generateAPR = rdoGenerateAPR.Checked;
        }

        private void chbUseSAPRBaseline_CheckedChanged(object sender, EventArgs e)
        {
            if (chbUseSAPRBaseline.Checked) gboUseSAPRBaseline.Enabled = true;
            else gboUseSAPRBaseline.Enabled = false;

            useSAPRAsBaseline = chbUseSAPRBaseline.Checked;
        }

        private void chbFineTuneAPR_CheckedChanged(object sender, EventArgs e)
        {
            if (chbFineTuneAPR.Checked) gboAPRFineTuning.Enabled = true;
            else gboAPRFineTuning.Enabled = false;

            fineTuneAPR = chbFineTuneAPR.Checked;
        }

        private void tabPagePromisReporting_Enter(object sender, EventArgs e)
        {
            if (!tabPagePromisReportingLoaded)
            {
                GenerateSAPRComboBox();
                GenerateAPRComboBox();

                tabPagePromisReportingLoaded = true;
            }
        }

        private void btnGeneratePromisReport_Click(object sender, EventArgs e)
        {
            btnGeneratePromisReport.Enabled = false;
            btnCancelPromisReport.Enabled = true;
            gboSAPR.Enabled = false;
            gboAPR.Enabled = false;
            rdoGenerateAPR.Enabled = false;
            rdoGenerateSAPR.Enabled = false;
            excelTemplateReports.Clear();
            Application.DoEvents();

            var templates = GetSelectedTemplateReports();
            foreach (var t in templates)
            {
                if (t.TemplateID != ExcelTemplateReport.INVALID_TEMPLATE_REPORT_ID)
                {
                    excelTemplateReports.Add(t);
                }
            }

            if (excelTemplateReports.Count > 0)
            {
                FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();
                folderBrowserDialog.ShowNewFolderButton = true;
                folderBrowserDialog.Description = "Select the folder for putting output files";
#if DEBUG
                DialogResult response = System.Windows.Forms.DialogResult.OK;
                folderBrowserDialog.SelectedPath = DEBUGGING_OUTPUT_FOLDER;
#else
                DialogResult response = folderBrowserDialog.ShowDialog(this);
#endif

                if (response == System.Windows.Forms.DialogResult.OK)
                {
                    m_SelectedFilePath = folderBrowserDialog.SelectedPath;
                    
                    progressPromisStatus.Visible = true;
                    progressPromisStatus.Minimum = 0;
                    lblPromisStatus.Visible = true;
                    lblPromisStatus.Text = String.Empty;

                    ListBoxItem item = null;
                    generateSAPR = rdoGenerateSAPR.Checked;
                    if (rdoGenerateSAPR.Checked)
                    {
                        sapr_endPeriod = SAPR_END_MONTH;
                        sapr_startPeriod = SAPR_START_MONTH;
                        item = (ListBoxItem)cboSAPRDuration.SelectedItem;
                        int y = Int32.Parse(item.Value.ToString());

                        string Q4 = string.Format("{0} Q4", y);
                        string Q1 = string.Format("{0} Q1", y + 1);
                        quarters = new string[] { Q4, Q1 };
                    }

                    generateAPR = rdoGenerateAPR.Checked;
                    if (rdoGenerateAPR.Checked)
                    {
                        sapr_endPeriod = APR_END_MONTH;
                        sapr_startPeriod = APR_START_MONTH;
                        item = (ListBoxItem)cboAPRDuration.SelectedItem;
                        int y = Int32.Parse(item.Value.ToString());

                        string Q4 = string.Format("{0} Q4", y);
                        string Q1 = string.Format("{0} Q1", y + 1);
                        string Q2 = string.Format("{0} Q2", y + 1);
                        string Q3 = string.Format("{0} Q3", y + 1);
                        quarters = new string[] { Q4, Q1, Q2, Q3 };
                    }

                    int year = Int32.Parse(item.Value.ToString());
                    sapr_startYear = year;
                    sapr_endYear = year + 1;

                    if (chbUseSAPRBaseline.Checked && generateAPR)
                    {
                        sapr_useBaselineData = (rtxtSelectedSAPRBaselineTemplates.Tag != null);
                        sapr_startYear = sapr_endYear;
                        sapr_startPeriod = SECOND_SAPR_START_MONTH;

                        string Q2 = string.Format("{0} Q2", sapr_startYear);
                        string Q3 = string.Format("{0} Q3", sapr_startYear);
                        quarters = new string[] { Q2, Q3 };

                        if (chbFineTuneAPR.Checked)
                        {
                            fineTuneAPR = true;
                            if (rdoAPRUseMaximum.Checked)
                            {
                                sapr_useMax_startDate = DateTime.Parse(String.Format("{0}-{1}-01", year, APR_START_MONTH));
                                sapr_useMax_endDate = DateTime.Parse(String.Format("{0}-{1}-28", year + 1, APR_END_MONTH));
                                sapr_useMaximumValues = true;

                                string q4 = string.Format("{0} Q4", year);
                                string q1 = string.Format("{0} Q1", year + 1);
                                string q2 = string.Format("{0} Q2", year + 1);
                                string q3 = string.Format("{0} Q3", year + 1);
                                max_quarters = new string[] { q4, q1, q2, q3 };
                            }
                            else sapr_useMaximumValues = false;
                        }
                        else fineTuneAPR = false;
                    }
                    else sapr_useBaselineData = false;
 
                    #region Determining the initial calculating method
                    var r = excelTemplateReports[0];
                    if (r.CalculatingMethod.HasValue)
                    {
                        if (r.CalculatingMethod == DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_COP14)
                        {
                            calculatingMethod = DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_COP14;
                        }
                        else calculatingMethod = DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_INVALID;
                    }
                    else calculatingMethod = DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_DEFAULT;
                    
                    foreach (var t in excelTemplateReports)
                    {
                        if (calculatingMethod == DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_INVALID) break;
                        else
                        {
                            if (t.CalculatingMethod.HasValue)
                            {
                                if (calculatingMethod != t.CalculatingMethod.Value)
                                {
                                    // The selection of report templates consists of more
                                    // than one template with their own calculation methods.
                                    // This is not currently supported.
                                    calculatingMethod = DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_INVALID;
                                    break;
                                }
                            }
                            else
                            {
                                // If not value the default method is used.
                                if (calculatingMethod != DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_DEFAULT)
                                {
                                    // For some reason one there was no specific calculating method, 
                                    // so the default method was selected.
                                    // but this template is suggesting its method to be used together
                                    // with default method.
                                    // This is not currently supported.
                                    calculatingMethod = DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_INVALID;
                                    break;
                                }
                            }
                        }
                    }
                    #endregion

                    if (calculatingMethod == DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_INVALID)
                    {
                        MessageBox.Show(this, 
                            "Please select templates which are relatively similar in structure", 
                            AppConfiguration.ApplicationName,
                            MessageBoxButtons.OK, MessageBoxIcon.Warning);

                        btnGeneratePromisReport.Enabled = true;
                        btnCancelPromisReport.Enabled = false;
                        gboSAPR.Enabled = rdoGenerateSAPR.Checked;
                        gboAPR.Enabled = rdoGenerateAPR.Checked;
                        rdoGenerateAPR.Enabled = true;
                        rdoGenerateSAPR.Enabled = true;
                    }
                    else backgroundWorkerPromis.RunWorkerAsync();
                }
                else
                {
                    btnGeneratePromisReport.Enabled = true;
                    btnCancelPromisReport.Enabled = false;
                    gboSAPR.Enabled = rdoGenerateSAPR.Checked;
                    gboAPR.Enabled = rdoGenerateAPR.Checked;
                    rdoGenerateAPR.Enabled = true;
                    rdoGenerateSAPR.Enabled = true;
                }
            }
            else
            {
                MessageBox.Show(this, "Please select at least one valid template", AppConfiguration.ApplicationName,
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);

                btnGeneratePromisReport.Enabled = true;
                btnCancelPromisReport.Enabled = false;
                gboSAPR.Enabled = rdoGenerateSAPR.Checked;
                gboAPR.Enabled = rdoGenerateAPR.Checked;
                rdoGenerateAPR.Enabled = true;
                rdoGenerateSAPR.Enabled = true;
            }
        }

        private void btnCancelPromisReport_Click(object sender, EventArgs e)
        {
            btnCancelPromisReport.Enabled = false;
            backgroundWorkerPromis.CancelAsync();
        }

        private void btnSelectSAPRTemplateFiles_Click(object sender, EventArgs e)
        {
            btnSelectSAPRTemplateFiles.Enabled = false;
            btnGeneratePromisReport.Enabled = false;
            Application.DoEvents();
            this.Cursor = Cursors.WaitCursor;

            try
            {
                var editor = new PromisTemplateSelector(ExcelTemplateType.SAPR, false);
                if (btnSelectSAPRTemplateFiles.Tag != null)
                {
                    var selectedTemplates = (List<TemplateSelectorViewModel>)btnSelectSAPRTemplateFiles.Tag;
                    editor.AddTemplates(selectedTemplates);
                }

                var response = editor.ShowDialog(this);
                if (response == DialogResult.OK)
                {
                    DisplayTemplatesInRichText(rtxtSelectedSAPRTemplates, editor.SelectedTemplates);
                    btnSelectSAPRTemplateFiles.Tag = editor.SelectedTemplates;
                }
            }
            catch (Exception ex)
            {
                DisplayErrorMessage(ex);
            }
            finally
            {
                btnSelectSAPRTemplateFiles.Enabled = true;
                btnGeneratePromisReport.Enabled = true;
                this.Cursor = Cursors.Default;
            }
        }

        private void btnSelectAPRTemplateFiles_Click(object sender, EventArgs e)
        {
            btnSelectAPRTemplateFiles.Enabled = false;
            btnGeneratePromisReport.Enabled = false;
            Application.DoEvents();
            this.Cursor = Cursors.WaitCursor;

            try
            {
                var editor = new PromisTemplateSelector(ExcelTemplateType.APR, false);
                if (btnSelectAPRTemplateFiles.Tag != null)
                {
                    var selectedTemplates = (List<TemplateSelectorViewModel>)btnSelectAPRTemplateFiles.Tag;
                    editor.AddTemplates(selectedTemplates);
                }

                var response = editor.ShowDialog(this);
                if (response == DialogResult.OK)
                {
                    DisplayTemplatesInRichText(rtxtSelectedAPRTemplates, editor.SelectedTemplates);
                    btnSelectAPRTemplateFiles.Tag = editor.SelectedTemplates;
                }
            }
            catch (Exception ex)
            {
                DisplayErrorMessage(ex);
            }
            finally
            {
                btnSelectAPRTemplateFiles.Enabled = true;
                btnGeneratePromisReport.Enabled = true;
                this.Cursor = Cursors.Default;
            }
        }

        private void lklSelectSAPRBaselineTemplates_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            btnSelectAPRTemplateFiles.Enabled = false;
            btnGeneratePromisReport.Enabled = false;
            rtxtSelectedSAPRBaselineTemplates.Enabled = false;
            Application.DoEvents();
            this.Cursor = Cursors.WaitCursor;

            try
            {
                var editor = new PromisTemplateSelector(ExcelTemplateType.SAPR, true);
                if (rtxtSelectedSAPRBaselineTemplates.Tag != null)
                {
                    var selectedTemplates = (List<TemplateSelectorViewModel>)rtxtSelectedSAPRBaselineTemplates.Tag;
                    editor.AddTemplates(selectedTemplates);
                }

                var response = editor.ShowDialog(this);
                if (response == DialogResult.OK)
                {
                    DisplayTemplatesInRichText(rtxtSelectedSAPRBaselineTemplates, editor.SelectedTemplates);
                    rtxtSelectedSAPRBaselineTemplates.Tag = editor.SelectedTemplates;
                }
            }
            catch (Exception ex)
            {
                DisplayErrorMessage(ex);
            }
            finally
            {
                btnSelectAPRTemplateFiles.Enabled = true;
                btnGeneratePromisReport.Enabled = true;
                rtxtSelectedSAPRBaselineTemplates.Enabled = true;
                this.Cursor = Cursors.Default;
            }
        }
        #endregion

        #region BackgroundWorker - PROMIS
        private void backgroundWorkerPromis_DoWork(object sender, DoWorkEventArgs e)
        {
            if (calculatingMethod == DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_DEFAULT)
            {
                operationSucceeded = GeneratePromiseSAPR_APR();
            }

            if (calculatingMethod == DataModels.Models.OtherSystemsFacilityDetail.CALCULATING_METHOD_COP14)
            {
                operationSucceeded = GeneratePromiseSAPR_APR_COP14();
            }
            
            if (backgroundWorkerPromis.CancellationPending)
            {
                e.Cancel = true;
                return;
            }
        }

        private void backgroundWorkerPromis_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {            
            try
            {
                SAPRProgress progressInfo = (SAPRProgress)e.UserState;
                toolStripStatusLabel.Text = progressInfo.log;
                lblPromisStatus.Text = progressInfo.log;
                if (e.ProgressPercentage == -1)
                {
                    progressPromisStatus.Style = ProgressBarStyle.Marquee;
                    progressPromisStatus.Minimum = 0;
                    progressPromisStatus.Maximum = 100;
                    progressPromisStatus.Value = 45;
                }
                else
                {
                    progressPromisStatus.Minimum = progressInfo.minValue;
                    progressPromisStatus.Maximum = progressInfo.maxValue;
                    progressPromisStatus.Value = progressInfo.value;
                    progressPromisStatus.Style = ProgressBarStyle.Continuous;
                }
            }
            catch
            {
                toolStripStatusLabel.Text = e.UserState.ToString();
                lblPromisStatus.Text = e.UserState.ToString();
            }
        }

        private void backgroundWorkerPromis_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            btnGeneratePromisReport.Enabled = true;
            progressPromisStatus.Visible = false;
            lblPromisStatus.Visible = false;

            if (e.Cancelled)
            {
                lblPromisStatus.Text = "Operation Cancelled";
                toolStripStatusLabel.Text = "Operation Cancelled";
                System.IO.File.Delete(m_SelectedFilePath);
            }
            else
            {
                if (operationSucceeded)
                {
                    lblPromisStatus.Text = "Operation completed";
                    toolStripStatusLabel.Text = "Operation completed";
                    Application.DoEvents();

                    DialogResult response = MessageBox.Show(
                        String.Format("Report has been generated successfully. It has been saved in {0}" +
                            ".\n\nDo you want to open it?", m_SelectedFilePath),
                        AppConfiguration.ApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (response == System.Windows.Forms.DialogResult.Yes)
                    {
                        try
                        {
                            System.Diagnostics.Process.Start(m_SelectedFilePath);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(
                                String.Format("Failed to open the file.{0}{0}Error details:{0}{1}",
                                    Environment.NewLine, ex.Message),
                                AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
                else
                {
                    MessageBox.Show("An error occured, operation did not complete successfully",
                        AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

            gboSAPR.Enabled = rdoGenerateSAPR.Checked;
            gboAPR.Enabled = rdoGenerateAPR.Checked;
            btnCancelPromisReport.Enabled = false;
            rdoGenerateAPR.Enabled = true;
            rdoGenerateSAPR.Enabled = true;
        }
        #endregion
    }
}
